﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using 开单.Model.基础;
using System.Data;
using System.Data.OleDb;
using winform;

namespace 开单.Dao.基础
{
    public class HpxxDao
    {
        HpxxEntity hpxxEntity = new HpxxEntity();
        DataTable dt = new DataTable();
        /// <summary>
        /// 添加货品
        /// </summary>
        /// <param name="HpxxEntity">货品实体类</param>
        public void AddHpxx(HpxxEntity hpxxEntity)
        {
            string sql = string.Empty;
            OleDbParameter[] parameters = {
                     new OleDbParameter("@hp_pm", hpxxEntity.hp_pm),
                     new OleDbParameter("@hp_dj", hpxxEntity.hp_dj),
                     new OleDbParameter("@hp_dw", hpxxEntity.hp_dw),
                     new OleDbParameter("@hp_gg", hpxxEntity.hp_gg),
                     new OleDbParameter("@hp_jp", hpxxEntity.hp_jp)};

            sql = "insert into jc_hpxx(hp_pm,hp_dj,hp_dw,hp_gg,hp_jp) values " +
                    "(@hp_pm,@hp_dj,@hp_dw,@hp_gg,@hp_jp)";

            DbHelperOleDb.ExecuteSql(sql, parameters);
        }

        /// <summary>
        /// 删除货品
        /// </summary>
        /// <param name="hp_code">货品编码</param>
        public void DelHpxx(object hp_code)
        {
            string sql = string.Empty;
            OleDbParameter[] parameters = {
                     new OleDbParameter("@hp_code", hp_code)};

            sql = "delete from jc_hpxx where hp_code=@hp_code ";

            DbHelperOleDb.ExecuteSql(sql, parameters);
        }

        /// <summary>
        /// 查询所有货品表
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public DataTable GetHpxxDt()
        {
            string sql = "select hp_code as 货品编码,hp_pm as 品名,hp_dj as 单价," +
                "hp_dw as 单位,hp_gg as 规格,hp_jp as 简拼 from jc_hpxx ";
            return DbHelperOleDb.ExecuteWithDataTable(sql, null);
        }

        /// <summary>
        /// 查询货品表
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public DataTable GetHpxxDtByHp_jp(string hp_jp)
        {
            hp_jp = "%" + hp_jp + "%";
            OleDbParameter[] paramethers={
                                             new OleDbParameter("@hp_jp",hp_jp)};
            string sql = "select hp_code as 货品编码,hp_pm as 品名,hp_dj as 单价," +
                "hp_dw as 单位,hp_gg as 规格,hp_jp as 简拼 from jc_hpxx "+
                "where hp_jp like @hp_jp  ";
            return DbHelperOleDb.ExecuteWithDataTable(sql, paramethers);
        }

        /// <summary>
        /// 查询货品信息
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public DataTable GetHpxxDtByHpName(string hp_name)
        {
            OleDbParameter[] paramethers ={
                                             new OleDbParameter("@hp_name",hp_name)};
            string sql = "select hp_code as 货品编码,hp_pm as 品名,hp_dj as 单价," +
                   "hp_dw as 单位,hp_gg as 规格,hp_jp as 简拼 from jc_hpxx " +
                   "where hp_pm = @hp_name  ";
            return DbHelperOleDb.ExecuteWithDataTable(sql, paramethers);
        }

        /// <summary>
        /// 查询货品信息
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public HpxxEntity GetHpxxEntityByHpName(string hp_name)
        {
            OleDbParameter[] paramethers ={
                                             new OleDbParameter("@hp_name",hp_name)};
            string sql = "select hp_code as 货品编码,hp_pm as 品名,hp_dj as 单价," +
                   "hp_dw as 单位,hp_gg as 规格,hp_jp as 简拼 from jc_hpxx " +
                   "where hp_pm = @hp_name  ";
            dt = DbHelperOleDb.ExecuteWithDataTable(sql, paramethers);
            if (dt.Rows.Count == 0) { return null; }
            hpxxEntity.hp_code =Convert.ToInt32( dt.Rows[0][0]);
            hpxxEntity.hp_pm = dt.Rows[0][1].ToString();
            hpxxEntity.hp_dj = Convert.ToDecimal(dt.Rows[0][2]);
            hpxxEntity.hp_dw = dt.Rows[0][3].ToString();
            hpxxEntity.hp_gg = dt.Rows[0][4].ToString();
            hpxxEntity.hp_jp = dt.Rows[0][5].ToString();
            return hpxxEntity;
        }

        /// <summary>
        /// 更新货品信息
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public void UpdataHpxxByHp_code(HpxxEntity hpxxEntity)
        {
            OleDbParameter[] parameters = {
                new OleDbParameter("@hp_pm", hpxxEntity.hp_pm),
                     new OleDbParameter("@hp_dj", hpxxEntity.hp_dj),
                     new OleDbParameter("@hp_dw", hpxxEntity.hp_dw),
                     new OleDbParameter("@hp_gg", hpxxEntity.hp_gg),
                     new OleDbParameter("@hp_jp", hpxxEntity.hp_jp),
                     new OleDbParameter("@hp_code", hpxxEntity.hp_code)};
            string sql = "update jc_hpxx set hp_pm=@hp_pm,hp_dj=@hp_dj,hp_dw=@hp_dw,"+
                            "hp_gg=@hp_gg ,hp_jp=@hp_jp where hp_code=@hp_code ";
            DbHelperOleDb.ExecuteSql(sql, parameters);
        }

        //public int getInfoCount()
        //{
        //    string sql = "select count(*) from jc_hpxx ";
        //    dt = DbHelperOleDb.ExecuteWithDataTable(sql, null);
        //    int i = Convert.ToInt32(dt.Rows[0][0]);
        //    return i;
        //}

    }
}
